/**
 * Project Name:jbase
 * File Name:DerbyDBServiceProvider.java
 * Package Name:jbase.project.system.net.http.websearcher
 * Date:2017年4月30日下午9:03:01
 * Copyright (c) 2017, chenzhou1025@126.com All Rights Reserved.
 *
*/

package jbase.project.system.net.http.websearcher;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jbase.project.system.core.support.LocalService;
import jbase.project.system.db.Derby;
import jbase.project.system.exception.SysException;

/**
 * ClassName:DerbyDBServiceProvider <br/>
 * Function: TODO ADD FUNCTION. <br/>
 * Reason: TODO ADD REASON. <br/>
 * Date: 2017年4月30日 下午9:03:01 <br/>
 * 
 * @author Licheng
 * @version
 * @since JDK 1.8
 * @see
 */
public class DerbyDBServiceProvider implements TaskServiceProvider {

	public final static String TASK_STATUS_INIT = "INIT";
	public final static String TASK_STATUS_FINISHED = "FINISHED";
	public final static String TASK_STATUS_ERROR = "ERROR";
	public final static String TASK_STATUS_SOURCE_DOWNLOAD = "DOWNLOADED";

	private Derby db = null;
	private String output;

	private String CONFIG_TABLE_DDL = "CREATE TABLE CONFIG(ID VARCHAR(50) NOT NULL,NAME VARCHAR(255) NOT NULL,DESCR VARCHAR(255) NOT NULL)";
	private String TASKS_TABLE_DDL = "CREATE TABLE TASKS(ID VARCHAR(50) NOT NULL,URL VARCHAR(1024) NOT NULL,STATUS VARCHAR(32) NOT NULL,CONTENT_FILE_NAME VARCHAR(4096))";

	private String QUERY_URL_COUNT = "select count(ID) from TASKS where URL = ?";
	private String ADD_TASK_SQL = "insert into TASKS(ID,URL,STATUS,CONTENT_FILE_NAME)values(?,?,?,?)";
	private String UPD_TASK_STATUS_BY_URL_SQL = "update TASKS set STATUS = ? where URL = ?";
	private String UPD_TASK_BY_URL_SQL = "update TASKS set STATUS = ?, CONTENT_FILE_NAME = ? where URL = ?";
	private String QUERY_TASK_STATUS_INIT = "select ID,URL,STATUS,CONTENT_FILE_NAME from TASKS where STATUS='INIT'";
	private String DELETE_TASK_BYID = "delete from TASKS where ID = ?";
	private String QUERY_TASK_COUNT_BY_URL_STATUS_SOURCE_DOWNLOAD_SQL = "select count(ID) from TASKS where URL = ? and STATUS = ?";

	private String INSERT_CONFIG_SQL = "insert into CONFIG(ID,NAME,DESCR)values(?,?,?)";
	private String QUERY_CONFIG_SQL = "select ID,NAME,DESCR from CONFIG";

	private LinkedList<String> allowRegex = new LinkedList<>();
	private LinkedList<String> refuseRegex = new LinkedList<>();
	private LinkedList<String> downRegex = new LinkedList<>();
	private Map<String, String> config = new HashMap<>();

	public DerbyDBServiceProvider(String dbPath, String dataPath)
			throws ClassNotFoundException, SQLException, SysException {
		File dataDir = new File(dataPath);
		if (!dataDir.exists()) {
			dataDir.mkdirs();
		} else if (dataDir.isFile()) {
			throw new SysException("名称被占用：" + dataPath);
		}
		this.output = dataPath;
		this.db = new Derby(dbPath, true);
		Connection conn = db.getConnection();
		DatabaseMetaData dbMetadata = conn.getMetaData();
		ResultSet tables = dbMetadata.getTables(null, null, null, new String[] { "TABLE" });
		boolean EXIST_TASKS_TABLE = false;
		boolean EXIST_CONFIG_TABLE = false;
		while (tables.next()) {
			if (tables.getString("TABLE_NAME").toUpperCase().equals("TASKS")) {
				EXIST_TASKS_TABLE = true;
			}
			if (tables.getString("TABLE_NAME").toUpperCase().equals("CONFIG")) {
				EXIST_CONFIG_TABLE = true;
			}
		}
		Statement s = conn.createStatement();
		if (!EXIST_TASKS_TABLE) {
			s.executeUpdate(TASKS_TABLE_DDL);
		}
		if (!EXIST_CONFIG_TABLE) {
			s.executeUpdate(CONFIG_TABLE_DDL);
		}

		ResultSet rs = conn.createStatement().executeQuery(QUERY_CONFIG_SQL);
		while (rs.next()) {
			switch (rs.getString("NAME")) {
			case "allow":
				allowRegex.add(rs.getString("DESCR"));
				break;
			case "refuse":
				refuseRegex.add(rs.getString("DESCR"));
				break;
			case "down":
				downRegex.add(rs.getString("DESCR"));
				break;
			default:
				config.put(rs.getString("NAME"), rs.getString("DESCR"));
				break;
			}
		}
		conn.close();
	}

	public void addConfig(String name, String desc) {
		Connection conn = null;
		try {
			conn = this.db.getConnection();
			PreparedStatement exec = conn.prepareStatement(INSERT_CONFIG_SQL);
			exec.setString(1, LocalService.uuid());
			exec.setString(2, name);
			exec.setString(3, desc);
			exec.executeUpdate();
			return;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		throw new RuntimeException();
	}

	@Override
	public boolean isExists(String url) {
		Connection conn = null;
		try {
			conn = this.db.getConnection();
			PreparedStatement exec = conn.prepareStatement(QUERY_URL_COUNT);
			exec.setString(1, url);
			ResultSet rs = exec.executeQuery();
			boolean is_exists = false;
			while (rs.next()) {
				int count = rs.getInt(1);
				if (count > 0) {
					is_exists = true;
				} else {
					is_exists = false;
				}
			}
			return is_exists;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		throw new RuntimeException();
	}

	@Override
	public void addURLToTasks(String url) {
		Connection conn = null;
		try {
			conn = this.db.getConnection();
			PreparedStatement exec = conn.prepareStatement(ADD_TASK_SQL);
			exec.setString(1, LocalService.uuid());
			exec.setString(2, url);
			exec.setString(3, TASK_STATUS_INIT);
			exec.setString(4, "");
			exec.executeUpdate();
			return;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		throw new RuntimeException();
	}

	@Override
	public void finishURL(String url) {
		Connection conn = null;
		PreparedStatement exec = null;
		try {
			conn = this.db.getConnection();
			exec = conn.prepareStatement(UPD_TASK_STATUS_BY_URL_SQL);
			exec.setString(1, TASK_STATUS_FINISHED);
			exec.setString(2, url);
			int updated = exec.executeUpdate();
			if (updated == 0) {
				exec = conn.prepareStatement(ADD_TASK_SQL);
				exec.setString(1, LocalService.uuid());
				exec.setString(2, url);
				exec.setString(3, TASK_STATUS_FINISHED);
				exec.setString(4, "");
				exec.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void errorURL(String url) {
		Connection conn = null;
		PreparedStatement exec = null;
		try {
			conn = this.db.getConnection();
			exec = conn.prepareStatement(UPD_TASK_STATUS_BY_URL_SQL);
			exec.setString(1, TASK_STATUS_ERROR);
			exec.setString(2, url);
			int updated = exec.executeUpdate();
			if (updated == 0) {
				exec = conn.prepareStatement(ADD_TASK_SQL);
				exec.setString(1, LocalService.uuid());
				exec.setString(2, url);
				exec.setString(3, TASK_STATUS_ERROR);
				exec.setString(4, "");
				exec.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	@Override
	public String getNextURL() {
		Connection conn = null;
		PreparedStatement exec = null;
		try {
			conn = this.db.getConnection();
			exec = conn.prepareStatement(QUERY_TASK_STATUS_INIT);
			ResultSet rs = exec.executeQuery();
			if (rs.next()) {
				String id = rs.getString("ID");
				String url = rs.getString("URL");
				PreparedStatement p = conn.prepareStatement(DELETE_TASK_BYID);
				p.setString(1, id);
				p.executeUpdate();
				return url;
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		throw new RuntimeException();
	}

	@Override
	public boolean isURLInTaskRange(String url) {
		return isPassTaskChecked(url);
	}

	private boolean isPassTaskChecked(String src) {
		boolean pass = true;
		int size = 0;
		Pattern p = null;
		Matcher m = null;

		size = allowRegex.size();
		for (int i = 0; i < size; i++) {
			p = Pattern.compile(allowRegex.get(i));
			m = p.matcher(src);
			if (!m.matches()) {
				pass = false;
				break;
			}
		}
		if (pass) {
			size = refuseRegex.size();
			for (int i = 0; i < size; i++) {
				p = Pattern.compile(refuseRegex.get(i));
				m = p.matcher(src);
				if (m.matches()) {
					pass = false;
					break;
				}
			}
		}
		return pass;
	}

	@Override
	public boolean save(final String url, final byte[] content) {
		Connection conn = null;
		PreparedStatement exec = null;
		try {
			conn = this.db.getConnection();
			exec = conn.prepareStatement(QUERY_TASK_COUNT_BY_URL_STATUS_SOURCE_DOWNLOAD_SQL);
			exec.setString(1, url);
			exec.setString(2, TASK_STATUS_SOURCE_DOWNLOAD);
			ResultSet rs = exec.executeQuery();
			if (rs.next()) {
				int count = rs.getInt(1);
				if (count == 0) {
					String fileName = this.output + "\\" + LocalService.uuid() + ".jpg";
					exec = conn.prepareStatement(UPD_TASK_BY_URL_SQL);
					exec.setString(1, TASK_STATUS_SOURCE_DOWNLOAD);
					exec.setString(2, fileName);
					exec.setString(3, url);
					int updated = exec.executeUpdate();
					if (updated == 0) {
						exec = conn.prepareStatement(ADD_TASK_SQL);
						exec.setString(1, LocalService.uuid());
						exec.setString(2, url);
						exec.setString(3, TASK_STATUS_SOURCE_DOWNLOAD);
						exec.setString(4, fileName);
						exec.executeUpdate();
					}
					OutputStream os = null;
					try {
						os = new FileOutputStream(fileName);
						os.write(content);
						os.close();
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						try {
							os.close();
						} catch (IOException e) {
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	@Override
	public boolean isDownLoadSource(String url) {
		return isDownSource(url);
	}

	private boolean isDownSource(String href) {
		boolean pass = false;
		for (String reg : downRegex) {
			if (href.matches(reg)) {
				pass = true;
			}
		}
		return pass;
	}

}
